package com.snoweagler.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;

/**
 * 作者：yangchao
 * 时间:18/3/1
 **/
public class PoiUtil {

    private static int aa;
    public static int getAa() {
        return aa;
    }

    public static void setAa(int aa) {
        PoiUtil.aa = aa;
    }

    /**
     * @param os
     * @param headers [用户名，邮箱，手机号]
     * @param fields  [name,email,mobile]
     * @param data
     */
    public static void exportExecl(OutputStream os, List<String> headers, List<String> fields, List data) {


        try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("用户信息");

            //表头
            Row header = sheet.createRow(0);
            CellStyle cellStyle = wb.createCellStyle();

            cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            for (int i = 0; i < headers.size(); i++) {
                Cell cell = header.createCell(i);
                cell.setCellValue(headers.get(i));
            }

            for (int i = 0; i < data.size(); i++) {
                Row row = sheet.createRow(i + 1);
                Object o = data.get(i);
                for (int j = 0; j < fields.size(); j++) {
                    Cell cell = row.createCell(j);
                    String s = fields.get(j);
                    Field field = o.getClass().getDeclaredField(s);
                    field.setAccessible(true);
                    Object fileValue = field.get(o);
                    if (fileValue != null)
                        cell.setCellValue(fileValue.toString());
                }
            }
            wb.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void inputFrom(String url){
        try {
            NPOIFSFileSystem fs = new NPOIFSFileSystem(new File(url));
            HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
            DataFormatter formatter = new DataFormatter();
            Sheet sheet1 = wb.getSheetAt(0);
            for (Row row : sheet1) {
                for (Cell cell : row) {
                    CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                    System.out.print(cellRef.formatAsString());
                    System.out.print(" - ");

                    // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
                    String text = formatter.formatCellValue(cell);
                    System.out.println(text);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
